#!/usr/bin/env node

const os= require('os');
const oracle = require('oracledb');
const fs = require('fs-extra');
const path = require('path');
const moment = require('moment');
const command = require('./js/command');
const program = require('commander');
const _ = require('lodash');
const configFile = path.join(os.homedir(), '.oracle_config');
const CACHE = {};
let _database = 'xe';
let _host = 'localhost';

let _verbose = false;
let _prettyShell = true;
let _limit = 10;
function createOracle (commands, options) {
    oracle.getConnection({
        user: 'autotest',
        password: 'owncloud',
        connectString : `${_host}:49161/${_database}`,
    }, async function(err, db) {
        if (err) {
            console.error(err.message);
            return process.exit(0);
        }
        const tables = await getTables(null, db);
        let allFileds = [];
        for (const table of tables) {
            const fields = await initFieldsOfTable(null, table, db);
            allFileds = [...allFileds, ...fields];
        }
        allFileds = _.uniq(allFileds);
        // 添加表，字段的自动完成
        function completer(line) {
            const words = line.replace(/\s,\s/g, ',').trimLeft().split(/\s/);
            const linePartial = _.last(_.last(words).split(','));
            let list = [];
            if (words.length <= 1) {
                list = ['select', 'update', 'delete'].filter((o) => o.startsWith(linePartial));
            } else {
                const preWord = _.toLower(_.nth(words, -2));
                if (_.includes(['from', 'update', 'into', 'ls', 's', 'm', 'r', 'c'], preWord)) {
                    list = tables.filter((o) => o.startsWith(linePartial));
                }  else {
                    const prePreWord = _.toLower(_.nth(words, -3));
                    if (_.includes(['s', 'm', 'r', 'c'], prePreWord)) {
                        const table = preWord;
                        const fields = CACHE[table];
                        list = fields.filter((o) => o.startsWith(linePartial));
                    } else {
                        const prePrePreWord = _.toLower(_.nth(words, -4));
                        if (_.includes(['s', 'm', 'c'], prePrePreWord)) {
                            const table = prePreWord;
                            const fields = CACHE[table];
                            list = fields.filter((o) => o.startsWith(linePartial));
                        } else {
                            list = allFileds.filter((o) => o.startsWith(linePartial));
                        }
                    }
                }
            }
            const found = list.filter((o) => o.startsWith(linePartial));
            return [found.length ? found : list, linePartial];
        }

        command(commands, { ...options, db, completer });
        _verbose = true;
    });
}
function formatResult(values, fields) {
    const obj = {};
    if (values instanceof Array) {
        return values.map(o=>formatResult(o));
    }
    for (const i in values) {
        const value = values[i];
        const key = fields[i];
        if (value instanceof Array) {
            obj[key] = formatResult(value);
        } else if (value instanceof Buffer) {
            obj[key] = !!value.readInt8(0);
        } else if (value instanceof Date) {
            obj[key] = moment(value).format('YYYY-MM-DD HH:mm:ss');
        } else {
            obj[key] = value;
        }
    }
    return obj;
}
function formatLine(line) {
    return line
    .replace(/"[^"]+"/g, o=>o.replace(' ', '&nbsp;'))
    .replace(/'[^']+'/g, o=>o.replace(' ', '&nbsp;'))
    .replace(/\s,\s/g, ',')
    .replace(/\s!=\s/g, '!=')
    .replace(/\s=\s/g, '=')
    .replace(/\s~\s/g, '~')
    .replace(/\s\|\s/g, '|')
    .replace(/\s&\s/g, '&')
    .replace(/\s#\s/g, '#')
    .trim().split(/\s/).filter(o=>o).map(o=>o.replace('&nbsp;', ' '));
}
function formatFields(table, line) {
    if (!line || line === '*') {
        return '*';
    }
    if (/\(/.test(line)) {
        return line;
    }
    let method = _.filter;
    if (line[0] === '-') {
        method = _.reject;
        line = line.substr(1);
    }
    let list = line.split(',');
    const allFields = getFieldsOfTable(table);
    list = list.map(o => new RegExp(o.replace('<', '^').replace('>', '$'), 'i'));

    return method(allFields, o=>_.some(list, r=>r.test(o))).join(',');
}
function formatCond(line, limit) {
    if (!line && !limit) {
        return '';
    }
    if (line) {
        const cond = 'WHERE ' + line
        .replace(/~[^&)|]+/g, o=>{let m = o[1]==='<'?o.slice(2):`%${o.substr(1)}`;return _.last(m)==='>'?` LIKE "${m.slice(0, -1)}"`:` LIKE "${m}%"`})
        .replace(/!=null|!=NULL/g, ' is not null ')
        .replace(/=null|=NULL/g, ' is null ')
        .replace(/(>=|<=|!=|=)([^&)|]+)/g, (o, m, n)=>`${m}${(m==='='||m==='!=')&&((n[0]!==`"`&& _.last(n)!==`"`)&&(n[0]!==`'`&& _.last(n)!==`'`))?`"${n}"`: n}`)
        .replace(/&/g, ' AND ')
        .replace(/\|/g, ' OR ')
        .replace(/#([\w,]*)/g, ' IN ($1)');
        return !limit ? cond : cond + ' AND ' + limit;
    }
    return 'WHERE ' + limit;
}
function formatSort(table, line) {
    if (!line) {
        return '';
    }
    const sort = [];
    const allFields = getFieldsOfTable(table);
    line = line.split(',');
    for (const i in line) {
        let item = line[i];
        let order = 'ASC';
        if (item[0] === '-') {
            item = item.substr(1);
            order = 'DESC';
        }
        if (_.includes(allFields, item)) {
            sort.push(`${item} ${order}`);
        }
    }
    if (!sort.length) {
        return '';
    }
    return `ORDER BY ${sort.join(',')}`;
}
function getList(ret, name) {
    const { metaData, rows } = ret;
    const index = _.findIndex(metaData, o=>o.name === name);
    if (index === -1) {
        return [];
    }
    return rows.map(o=>o[index]);
}
async function initFieldsOfTable(self, table, db) {
    CACHE[table] = getList(await querySQL(self, db, `select * from user_tab_columns where table_name='${table}'`), 'COLUMN_NAME');
    return CACHE[table];
}
async function getTables(self, db) {
    if (!CACHE._tables) {
        CACHE._tables = getList(await querySQL(self, db, 'select table_name from user_tables'), 'TABLE_NAME');
    }
    return CACHE._tables;
}
function getFieldsOfTable(table) {
    if (!CACHE[table]) {
        return [];
    }
    return CACHE[table];
}
function querySQL(self, db, query) {
    if (_verbose && self) {
        self.verbose(query);
    }
    return new Promise(resolve=>{
        db.execute(query, (err, results) => {
            if (err) {
                if (self) {
                    self.error(err);
                    self.prompt();
                }
                resolve([]);
            } else {
                resolve(results);
            }
        });
    })
}
async function executeSQL(self, line, db) {
    const result = await querySQL(self, db, line);
    const fields = result.metaData.map(o=>o.name);

    result.rows.forEach(o=>{
        self.showJson(formatResult(o, fields), _prettyShell);
    });
    self.prompt();
}
async function executeLine(line, { db }) {
    if (!line) {
        return this.prompt();
    }
    await executeSQL(this, line, db);
}
async function showTables(line, { db }) {
    if (!line) { // show tables
        this.print('tables:', 'red');
        results = await getTables(this, db);
        if (results) {
            this.print(results.join('  '), 'green');
            this.prompt();
        }
    } else { // show table fields
        line = line.replace(/[^\w]*/g, '');
        const fields = getFieldsOfTable(line);
        this.print(fields.join('  '), 'green');
        this.prompt();
    }
}
async function showTableRows (line, { db }) {
    if (!line) {
        return this.prompt();
    }
    let list = formatLine(line);
    const table = list[0];
    if (!table) {
        return this.error('invalid oper');
    }
    list = _.drop(list);

    let limit = '';
    let limitIndex = _.findIndex(list, o=>+o==o);
    if (limitIndex !== -1) {
        limit = +list[limitIndex];
        limit = limit ? `rownum<=${limit}` : '';
        list.splice(limitIndex, 1);
    } else {
        limit = `rownum<=${_limit}`;
    }

    let cond = '';
    let condIndex = _.findIndex(list, o=>/!=|>=|>\w|=|<=|<\w|~|#/.test(o));
    if (condIndex !== -1) {
        cond = list[condIndex];
        list.splice(condIndex, 1);
    }
    cond = formatCond(cond, limit);

    let _fields, _sort;
    if (list.length == 1) {
        _fields = list[0];
    } else if (list.length > 1) {
        _fields = list[0];
        _sort = list[1];
    }
    const fields = formatFields(table, _fields);
    const sort = formatSort(table, _sort);
    await executeSQL(this, `SELECT ${fields} FROM ${table} ${cond} ${sort}`, db);
}
async function modifyTable(line, { db }) {
    if (!line) {
        return this.prompt();
    }
    let list = formatLine(line);
    const table = list[0];
    if (!table) {
        return this.error('invalid oper');
    }
    list = _.drop(list);

    let limit = '';
    let limitIndex = _.findIndex(list, o=>+o==o);
    if (limitIndex !== -1) {
        limit = +list[limitIndex];
        limit = limit ? `rownum<=${limit}` : '';
        list.splice(limitIndex, 1);
    } else {
        limit = `rownum<=${_limit}`;
    }

    const setFields = list[0];
    if (!setFields) {
        return this.error('invalid oper');
    }
    list = _.drop(list);

    let cond = '';
    let condIndex = _.findIndex(list, o=>/!=|>=|>\w|=|<=|<\w|~|#/.test(o));
    if (condIndex !== -1) {
        cond = list[condIndex];
        list.splice(condIndex, 1);
    }
    cond = formatCond(cond, limit);

    const sort = formatSort(table, list[0]);

    await executeSQL(this, `UPDATE ${table} SET ${setFields} ${cond} ${sort}`, db);
}
async function removeTable(line, { db }) {
    if (!line) {
        return this.prompt();
    }
    let list = formatLine(line);
    const table = list[0];
    if (!table) {
        return this.error('invalid oper');
    }
    list = _.drop(list);

    let limit = '';
    let limitIndex = _.findIndex(list, o=>+o==o);
    if (limitIndex !== -1) {
        limit = +list[limitIndex];
        limit = limit ? `rownum<=${limit}` : '';
        list.splice(limitIndex, 1);
    } else {
        limit = `rownum<=${_limit}`;
    }

    let cond = '';
    let condIndex = _.findIndex(list, o=>/!=|>=|>\w|=|<=|<\w|~|#/.test(o));
    if (condIndex !== -1) {
        cond = list[condIndex];
        list.splice(condIndex, 1);
    }
    cond = formatCond(cond, limit);

    const sort = formatSort(table, list[0]);

    await executeSQL(this, `DELETE FROM ${table} ${cond} ${sort}`, db);
}
async function countTableRows(line, { db }) {
    if (!line) {
        return this.prompt();
    }
    let list = formatLine(line);
    const table = list[0];
    if (!table) {
        return this.error('invalid oper');
    }
    list = _.drop(list);

    let cond = '';
    let condIndex = _.findIndex(list, o=>/!=|>=|>\w|=|<=|<\w|~|#/.test(o));
    if (condIndex !== -1) {
        cond = formatCond(list[condIndex]);
        list.splice(condIndex, 1);
    }

    await executeSQL(this, `SELECT COUNT(*) FROM ${table} ${cond}`, db);
}
async function copyTableRows(line, { db }) {
    if (!line) {
        return this.prompt();
    }
    let list = formatLine(line);
    const table = list[0];
    if (!table) {
        return this.error('invalid oper');
    }
    list = _.drop(list);

    let limit = '';
    let limitIndex = _.findIndex(list, o=>+o==o);
    if (limitIndex !== -1) {
        limit = +list[limitIndex];
        limit = limit ? `rownum<=${limit}` : '';
        list.splice(limitIndex, 1);
    } else {
        limit = `rownum<=${_limit}`;
    }

    let sort = '';
    let sortIndex = _.findIndex(list, o=>/^-?\w+$/.test(o));
    if (sortIndex !== -1) {
        sort = formatSort(list[sortIndex]);
        list.splice(sortIndex, 1);
    }

    let _cond = '';
    let setFields = [];
    if (!list[1]) {
        setFields = [];
        _cond = list[0];
    } else {
        setFields = list[0].split(',').map(o=>o.split('='));
        _cond = list[1];
    }
    const cond = formatCond(_cond, limit);
    const docs = await querySQL(this, db, `SELECT * FROM ${table} ${cond} ${sort}`);
    const fields = getFieldsOfTable(table).filter(o=>o!=='id');
    for (const doc of docs) {
        const values = fields.map(o=>{
            const setField = _.find(setFields, m=>m[0]===o);
            if (setField) {
                let setValue = setField[1];
                const matches = setValue.match(/\$\w+/g);
                if (matches) {
                    const tmps = matches.map(o=>o.replace('\$', ''));
                    for (const tmp of tmps) {
                        setValue = setValue.replace('$'+tmp, doc[tmp]);
                    }
                }
                if (/[+-\\*/]/.test(setValue)) {
                    return eval(eval(setValue));
                }
                return setValue;
            }
            const item = doc[o];
            if (item instanceof Date) {
                return 'NULL';
            }
            if (doc[o] !== undefined) {
                return JSON.stringify(doc[o]);
            }
            return 'NULL';
        });
        await executeSQL(this, `INSERT INTO ${table} (${fields.join(',')}) VALUES (${values.join(',')})`, db);
    }
}
function togglePrettyShell() {
    _prettyShell = !_prettyShell;
    this.print('pretty format change to ' + _prettyShell);
    this.prompt();
}
function toggleVerbose() {
    _verbose = !_verbose;
    this.print('verbose format change to ' + _verbose);
    this.prompt();
}
function setDefaultLimit(line) {
    _limit = +line || 10;
    this.print('set limit to ' + _limit);
    this.prompt();
}
function showHelp () {
    this.print('commands:', 'blue');
    this.print('    <h|help>: show help');
    this.print('    <q|exit>: exit');
    this.print('    ls: showTables|ls -: showDatabases|ls table: showTableFields');
    this.print('    s(showTableRows): s table f1,f2>,<f3> (f1=xx&f2=yy)|f3=zz|f4~<xx>|f5#1,2,3 limit(-1:count) sortField[+-] [out=file] [r] [f1:$f1+1]');
    this.print('    m(modifyTable): m table f1:$f1+1;f2:xx;f3:(doc, {_, utils, moment})=>xx $cond limit sortField[+-]');
    this.print('    r(removeTable): # r table $cond limit sortField[+-]');
    this.print('    c(copyTableRows): c table $update $cond limit sortField[+-]');
    this.print('    _: togglePrettyShell');
    this.print('    v: toggleVerbose');
    this.print('    limit: setDefaultLimit');
    this.print('');
    this.prompt();
}
const COMMANDS = {
    'h|help': showHelp,
    'q|exit': function () { this.close() },
    'cls': function() { this.clear() },
    'ls': showTables,
    's': showTableRows,
    'm': modifyTable,
    'r': removeTable,
    't': countTableRows,
    'c': copyTableRows,
    '-': togglePrettyShell,
    'v': toggleVerbose,
    'limit': setDefaultLimit,
    'default': executeLine,
};


program
.version('1.0.0')
.option('-o, --host [host]', 'set host', '')
.option('-d, --database [database]', 'set database', '')
.option('-c, --config', 'show config', '')
.parse(process.argv);

let options = {};
try {
    options = fs.readJSONSync(configFile);
} catch (e) {}

_host = program.host || options.host || _host;
_database = program.database || options.database || _database;
if (options.host !== _host || options.database !== _database) {
    fs.writeJSONSync(configFile, { host: _host, database: _database });
}
if (program.config) {
    console.log({ host: _host, database: _database });
    process.exit(0);
}

createOracle(COMMANDS, { title: 'oracle', history: [ 's', 'm', 'r', 't', 'c' ] });
